package com.hezhi.trafficcopsys.persistence.dao.impl;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.hezhi.framework.dao.DAOUtil;
import com.hezhi.framework.exception.DaoException;
import com.hezhi.framework.util.DateUtil;
import com.hezhi.trafficcopsys.domain.BreakRuleRecord;
import com.hezhi.trafficcopsys.domain.SearchCondition;
import com.hezhi.trafficcopsys.domain.SpecialCar;
import com.hezhi.trafficcopsys.persistence.dao.BreakRuleRecordDao;
import com.hezhi.trafficcopsys.util.Constants4sys;

/**
 * 
 * @author wudo
 *
 */
public class BreakRuleRecordDaoImpl implements BreakRuleRecordDao{

	@Override
	public List<BreakRuleRecord> searchForLikeCondition(SearchCondition searchCondition) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		List<BreakRuleRecord> lstResords = new ArrayList<BreakRuleRecord>();
		BreakRuleRecord record = null;
		try{
			if( null != searchCondition){
				StringBuffer sbSQL = new StringBuffer();
				sbSQL.append("select ID,HPZL,HPHM,LKMC,WFSJ from WFJL, LKXX where ( BJ IS NULL OR BJ != '2' ) AND LKXX.LKID = WFJL.LKID and HPHM !='无' ");
				//车牌号码
				if( null != searchCondition.getCarNum() && !"".equals(searchCondition.getCarNum())){
					sbSQL.append(" AND HPHM = '");
					sbSQL.append(searchCondition.getCarNum());
					sbSQL.append("' ");
				}
				
				//车辆类型
				if( null != searchCondition.getCarType() && !"".equals(searchCondition.getCarType())){
					sbSQL.append(" AND HPZL = '");
					sbSQL.append(searchCondition.getCarType());
					sbSQL.append("' ");
				}
				
				//违章查询开始时间
				if( null != searchCondition.getStartDate() && !"".equals(searchCondition.getStartDate())){
					//sbSQL.append(" AND to_char(WFSJ,'yyyy-MM-DD') >= '");
					sbSQL.append(" AND date_format(WFSJ,'%Y-%m-%d') >= '");
					sbSQL.append(searchCondition.getStartDate());
					sbSQL.append("' ");
				}
				
				//违章查询结束时间
				if( null != searchCondition.getEndDate() && !"".equals(searchCondition.getEndDate())){
					//sbSQL.append(" AND to_char(WFSJ,'yyyy-MM-DD') <= '");
					sbSQL.append(" AND date_format(WFSJ,'%Y-%m-%d') <= '");
					sbSQL.append(searchCondition.getEndDate());
					sbSQL.append("' ");
				}
				
				sbSQL.append(" ORDER BY  WFSJ DESC ");
				String sql = sbSQL.toString();
				
				conn = DAOUtil.getDBConnection();
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				int number = 1;
				while (rs.next()){
					record = new BreakRuleRecord();
					
					record.setNumber(number);
					record.setBreakRulePictureID(rs.getString(1));//违章图片ID
					
					//车辆类型
					if(Constants4sys.SMALL_CAR_FLG.equals(rs.getString(2))){
						record.setCarType(Constants4sys.SMALL_CAR);
					} else if(Constants4sys.BIG_CAR_FLG.equals(rs.getString(2))){
						record.setCarType(Constants4sys.BIG_CAR);
					}
					record.setCarNum(rs.getString(3));//车牌号码
					record.setBreakRuleLocation(rs.getString(4));//违章地点
					record.setBreakRuleTime(DateUtil.dateFormatter(rs.getTimestamp(5),Constants4sys.DATE_FORMATTER));//违章时间
					
					//违章代码
					record.setBreakRuleType("闯红灯");
					
					lstResords.add(record);
					number ++;
				}
			}
		} catch (Exception e){
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs, stmt, conn);
		}
		return lstResords;
	}

	/**
	 * 根据传入的特殊车牌号码进行查询。
	 */
	@Override
	public List<BreakRuleRecord> searchForSpecialCar(List<SpecialCar> listCarNums,String carType,String startDate,String endDate) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<BreakRuleRecord> lstResords = new ArrayList<BreakRuleRecord>();
		BreakRuleRecord record = null;
		try{
			if( listCarNums != null && listCarNums.size() > 0){
				conn = DAOUtil.getDBConnection();
				StringBuffer sbSQL = new StringBuffer();
				sbSQL.append("SELECT ID,HPZL,HPHM,LKMC,WFSJ FROM WFJL, LKXX WHERE ( BJ IS NULL OR BJ != '2' ) AND LKXX.LKID = WFJL.LKID and HPHM !='无' ");
				
				//车辆类型
				if( null != carType && !"".equals(carType)){
					sbSQL.append(" AND HPZL = '");
					sbSQL.append(carType);
					sbSQL.append("' ");
				}
				
				//违章查询开始时间
				if( null != startDate && !"".equals(startDate)){
					//sbSQL.append(" AND to_char(WFSJ,'yyyy-MM-DD') >= '");
					sbSQL.append(" AND date_format(WFSJ,'%Y-%m-%d') >= '");
					sbSQL.append(startDate);
					sbSQL.append("' ");
				}
				
				//违章查询结束时间
				if( null != endDate && !"".equals(endDate)){
					//sbSQL.append(" AND to_char(WFSJ,'yyyy-MM-DD') <= '");
					sbSQL.append(" AND date_format(WFSJ,'%Y-%m-%d') <= '");
					sbSQL.append(endDate);
					sbSQL.append("' ");
				}
				
				sbSQL.append(" AND HPHM in (  ");
				for(SpecialCar specialCar : listCarNums){
					sbSQL.append("'");
					sbSQL.append(specialCar.getCarNo());
					sbSQL.append("' ");
					sbSQL.append(",");
				}
				sbSQL.deleteCharAt(sbSQL.length() -1);
				sbSQL.append(")");
				sbSQL.append(" ORDER BY WFSJ DESC ");
				
				String sql = sbSQL.toString();
				
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				int number = 1;
				while (rs.next()){
					record = new BreakRuleRecord();
					
					record.setNumber(number);
					record.setBreakRulePictureID(rs.getString(1));//违章图片ID
					
					//车辆类型
					if(Constants4sys.SMALL_CAR_FLG.equals(rs.getString(2))){
						record.setCarType(Constants4sys.SMALL_CAR);
					} else if(Constants4sys.BIG_CAR_FLG.equals(rs.getString(2))){
						record.setCarType(Constants4sys.BIG_CAR);
					}
					record.setCarNum(rs.getString(3));//车牌号码
					record.setBreakRuleLocation(rs.getString(4));//违章地点
					record.setBreakRuleTime(DateUtil.dateFormatter(rs.getTimestamp(5),Constants4sys.DATE_FORMATTER));//违章时间
					
					//违章代码
					record.setBreakRuleType("闯红灯");
					
					lstResords.add(record);
					number ++;
				}
			}
		} catch (Exception e){
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs, stmt, conn);
		}
		return lstResords;
	}

	@Override
	public int update(String[] breakRulePictureIDs) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int updateRecords = 0;
		try{
			if( breakRulePictureIDs != null && breakRulePictureIDs.length > 0){
				conn = DAOUtil.getDBConnection();
				StringBuffer sbSQL = new StringBuffer();
				sbSQL.append("update WFJL set BJ = ? where ID in ( ");
				for(String breakRulePictureID : breakRulePictureIDs){
					sbSQL.append("'");
					sbSQL.append(breakRulePictureID);
					sbSQL.append("'");
					sbSQL.append(",");
				}
				String sql = sbSQL.substring(0, sbSQL.length() - 1 );
				sql  = sql + ")";
				
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, Constants4sys.DELETE_FLG);
				updateRecords = stmt.executeUpdate();
			}
		} catch (Exception e){
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs, stmt, conn);
		}
		return updateRecords;
	}

	@Override
	public byte[] showPicture(String pictureID) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		byte[] bytes = null;
		try{
			if( pictureID != null && !"".equals(pictureID)){
				conn = DAOUtil.getDBConnection();
				stmt = conn.prepareStatement("select TP from TP where ID = ?");
				stmt.setString(1, pictureID);
				rs = stmt.executeQuery();
				while (rs.next()){
					Blob b = rs.getBlob(1);
					bytes = b.getBytes(1, (int)b.length());
					//bytes = rs.getBytes(1);
				}
			}
		} catch (Exception e){
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs, stmt, conn);
		}
		return bytes;
	}
}
